/**
 * 
 */
package com.kandinfo.isbnmanage.repository.impl;

import java.text.SimpleDateFormat;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.kandinfo.base.common.repository.AliasToBeanResultTransformerEx;
import com.kandinfo.isbnmanage.dto.IsbnDealtReqDto;
import com.kandinfo.isbnmanage.dto.IsbnDealtResDto;
import com.kandinfo.isbnmanage.dto.IsbnManageReqDto;
import com.kandinfo.isbnmanage.dto.IsbnManageResDto;
import com.kandinfo.isbnmanage.repository.IsbnRepository;

/**
 * @author visitor03
 *
 */
@Repository
public class IsbnRepositoryImpl implements IsbnRepository {

	@Autowired
    private EntityManager entityManager;
	private SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	
	// ---------------------isbn manager query start-------------------
	@SuppressWarnings("unchecked")
	@Override
	public List<IsbnManageResDto> searchByCon(IsbnManageReqDto isbnManageReqDto) {
		StringBuffer sql = new StringBuffer(" select id, create_by createBy, update_by updateBy, create_ts createTs,");
		sql.append(" update_ts updateTs, barcode, treatment_opinion treatmentOpinion, order_count orderCount, receipt_count receiptCount, ");
		sql.append(" commit_ts commitTs, last_receipt_ts lastReceiptTs, scan_count scanCount, upload_count uploadCount, publish_count publishCount, ");
		sql.append(" comments, isbn_information_count isbnInformationCount, status, application_count applicationCount, province");
		sql.append(" from isbn_manage i ").append(createCondition(isbnManageReqDto, false));
		org.hibernate.Query query =  createQuery(sql.toString(), isbnManageReqDto, false).unwrap(org.hibernate.Query.class);
		query.setResultTransformer(new AliasToBeanResultTransformerEx(IsbnManageResDto.class));
		return query.list();
	}
	@Override
	public Long getIsbnManageCount(IsbnManageReqDto isbnManageReqDto) {
		String sql = "select count(1) from isbn_manage i " + createCondition(isbnManageReqDto, true);
		org.hibernate.Query query =  createQuery(sql, isbnManageReqDto, true).unwrap(org.hibernate.Query.class);
		return Long.parseLong(query.uniqueResult().toString());
	}
	private String createCondition(IsbnManageReqDto isbnManageReqDto, boolean isCount){
		StringBuffer sql = new StringBuffer(" where 1=1 ");
		if(isbnManageReqDto.getStatus() != null) {
			sql.append(" and status=:status ");
		}
		if (StringUtils.isNotEmpty(isbnManageReqDto.getStartTime())) {
			sql.append(" and commit_ts >= :startTime ");
		}
		if (StringUtils.isNotEmpty(isbnManageReqDto.getEndTime())) {
			sql.append(" and commit_ts <= :endTime");
		}
		if (StringUtils.isNotEmpty(isbnManageReqDto.getBarcode())) {
			sql.append(" and barcode = :barcode");
		}
		if(!isCount) {
			sql.append(" order by commitTs ");
			sql.append(" limit :limit,:pageSize ");
		}
		return sql.toString();
	}	
	private Query createQuery(String sql, IsbnManageReqDto isbnManageReqDto, boolean isCount) {
		Query nativeQuery = entityManager.createNativeQuery(sql);
		try {
			if (isbnManageReqDto.getStatus() != null) {
				nativeQuery.setParameter("status", isbnManageReqDto.getStatus());
			}
			if (StringUtils.isNotEmpty(isbnManageReqDto.getStartTime())) {
				nativeQuery.setParameter("startTime", format.parse(isbnManageReqDto.getStartTime()));
			}
			if (StringUtils.isNotEmpty(isbnManageReqDto.getEndTime())) {
				nativeQuery.setParameter("endTime", format.parse(isbnManageReqDto.getEndTime()));
			}
			if (StringUtils.isNotEmpty(isbnManageReqDto.getBarcode())) {
				nativeQuery.setParameter("barcode", isbnManageReqDto.getBarcode());
			}
			if(!isCount) {
				nativeQuery.setParameter("limit", isbnManageReqDto.getDisplayStart());
				nativeQuery.setParameter("pageSize", isbnManageReqDto.getDisplayLength());
			}
			
		} catch (Exception e) {
		}
		return nativeQuery;
	}
	// ---------------------isbn manager query end-------------------

	
	
	
	// ---------------------isbn dealt query start-------------------
	@SuppressWarnings("unchecked")
	@Override
	public List<IsbnDealtResDto> searchIsbnDealtByCon(IsbnDealtReqDto isbnDealtReqDto) {
		StringBuffer sql = new StringBuffer(" select i.id, i.create_by createBy, i.update_by updateBy, i.create_ts createTs, i.status isbnStatus, ");
		sql.append(" i.barcode, i.treatment_opinion treatmentOpinion,  ii.status infomationStatus ,ii.id isbnInformationId ");
		sql.append(" ,ii.name bookName, ii.comments, io.status orderStatus  ");
		sql.append(" from isbn_manage i left join isbn_information ii on i.barcode=ii.barcode ");
		sql.append(" LEFT JOIN	isbn_order io ON ii.id = io.isbn_information_id  ");
		sql.append(" ,isbn_distribute id ");
		sql.append(createCondition(isbnDealtReqDto, false));
		org.hibernate.Query query =  createQuery(sql.toString(), isbnDealtReqDto, false).unwrap(org.hibernate.Query.class);
		query.setResultTransformer(new AliasToBeanResultTransformerEx(IsbnDealtResDto.class));
		return query.list();
	}
	@Override
	public Long getIsbnDealtCount(IsbnDealtReqDto isbnDealtReqDto) {
		StringBuffer sql = new StringBuffer(" select count(1) ");
		sql.append(" from isbn_manage i left join isbn_information ii on i.barcode=ii.barcode ");
		sql.append(" LEFT JOIN	isbn_order io ON ii.id = io.isbn_information_id  ");
		sql.append(" ,isbn_distribute id ");
		sql.append(createCondition(isbnDealtReqDto, true));
		org.hibernate.Query query =  createQuery(sql.toString(), isbnDealtReqDto, true).unwrap(org.hibernate.Query.class);
		return Long.parseLong(query.uniqueResult().toString());
	}
	private String createCondition(IsbnDealtReqDto isbnDealtReqDto, boolean isCount){
		StringBuffer sql = new StringBuffer(" where ");
		sql.append(" i.barcode = id.barcode ");
		if(isbnDealtReqDto.getStatus() != null) {
			sql.append(" and i.status = :status ");
		}
		if(isbnDealtReqDto.getOrderStatus() != null) {
			sql.append(" and io.status = :orderStatus ");
		}
		if(StringUtils.isNotEmpty(isbnDealtReqDto.getTreatmentOpinion())) {
			sql.append(" and i.treatment_opinion = :treatmentOpinion ");
		}
		if (StringUtils.isNotEmpty(isbnDealtReqDto.getStartTime())) {
			sql.append(" and i.commit_ts >= :startTime ");
		}
		if (StringUtils.isNotEmpty(isbnDealtReqDto.getEndTime())) {
			sql.append(" and i.commit_ts <= :endTime");
		}
		if (StringUtils.isNotEmpty(isbnDealtReqDto.getBarcode())) {
			sql.append(" and i.barcode = :barcode");
		}
		if (isbnDealtReqDto.getCurUserId() != null) {
			sql.append(" and id.distribute_to = :distributeTo ");
		}
		if (StringUtils.isNotEmpty(isbnDealtReqDto.getWorkType())) {
			//工作类型查找
			if ("buy".equals(isbnDealtReqDto.getWorkType())) {
				sql.append(" and id.type = 0 ");
				sql.append(" and (ii.barcode is null or (ii.status = 0 and ii.barcode = id.barcode ))  ");
			}else if ("complete".equals(isbnDealtReqDto.getWorkType())) {
				sql.append(" and id.type = 1 ");
				sql.append(" and ii.status in (1,2) and ii.id = id.ref_key  ");
			}else if ("publish".equals(isbnDealtReqDto.getWorkType())) {
				sql.append(" and id.type = 2 ");
				sql.append(" and ii.status in (3,4,5,6) and ii.id = id.ref_key  ");
			}
		}
		if(!isCount) {
			sql.append(" limit :limit,:pageSize");
		}
		return sql.toString();
	}	
	private Query createQuery(String sql, IsbnDealtReqDto isbnDealtReqDto, boolean isCount) {
		Query nativeQuery = entityManager.createNativeQuery(sql);
		try {
			if (isbnDealtReqDto.getStatus() != null) {
				nativeQuery.setParameter("status", isbnDealtReqDto.getStatus());
			}
			if(isbnDealtReqDto.getOrderStatus() != null) {
				nativeQuery.setParameter("orderStatus", isbnDealtReqDto.getOrderStatus());
			}
			if(StringUtils.isNotEmpty(isbnDealtReqDto.getTreatmentOpinion())) {
				nativeQuery.setParameter("treatmentOpinion", isbnDealtReqDto.getTreatmentOpinion());
			}
			if (StringUtils.isNotEmpty(isbnDealtReqDto.getStartTime())) {
				nativeQuery.setParameter("startTime", format.parse(isbnDealtReqDto.getStartTime()));
			}
			if (StringUtils.isNotEmpty(isbnDealtReqDto.getEndTime())) {
				nativeQuery.setParameter("endTime", format.parse(isbnDealtReqDto.getEndTime()));
			}
			if (isbnDealtReqDto.getCurUserId() != null) {
				nativeQuery.setParameter("distributeTo", isbnDealtReqDto.getCurUserId());
			}
			if (StringUtils.isNotEmpty(isbnDealtReqDto.getBarcode())) {
				nativeQuery.setParameter("barcode", isbnDealtReqDto.getBarcode());
			}
			if(!isCount) {
				nativeQuery.setParameter("limit", isbnDealtReqDto.getDisplayStart());
				nativeQuery.setParameter("pageSize", isbnDealtReqDto.getDisplayLength());
			}
		} catch (Exception e) {
		}
		return nativeQuery;
	}
	// ---------------------isbn dealt query end-------------------

}
